package cn.quickcode.monitor.db;

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class DbHelper
{
	private final static String dbfile = System.getProperty("user.dir") + File.separator + "sys.dat";
	private final static String connstr = "jdbc:sqlite:" + dbfile;
	private final static void initdbfiles(Connection conn) throws SQLException
	{
		PreparedStatement pst = null;
		String[] dfs =
		{
			"Create  TABLE DBM(ID integer PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL,NAME varchar(100) UNIQUE NOT NULL,IP Varchar(100) NOT NULL,STATUS char(1) NOT NULL DEFAULT 0);",
			"Create  TABLE SRM(ID integer PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL,NAME varchar(100) UNIQUE NOT NULL,IP Varchar(100) NOT NULL,STATUS char(1) NOT NULL DEFAULT 0);"
		};
		for (String sql : dfs)
		{
			pst = conn.prepareStatement(sql);
			System.out.println(sql);
			pst.execute();
			pst.close();
			pst = null;
		}
		
	}
	private static boolean init=false;
	public static Connection getConnection() throws SQLException
	{
		Connection conn = null;
		try
		{
			Class.forName("org.sqlite.JDBC");
			if(!init)
			{
				File file = new File(dbfile);
				if(file.exists()) init = true; 
			}
			conn = DriverManager.getConnection(DbHelper.connstr);
			if(!init) initdbfiles(conn);
			return conn;
		}
		catch (ClassNotFoundException e)
		{
			e.printStackTrace();
			throw new SQLException("org.sqlite.JDBC:ClassNotFoundException");
		}
		catch (SQLException e)
		{
			throw new SQLException(e);
		}
	}

	public static void close(Connection conn)
	{
		if (conn != null)
		{
			try
			{
				conn.close();
				conn = null;
			}
			catch (SQLException e)
			{
				e.printStackTrace();
			}
		}
	}

	public static void close(PreparedStatement pst) throws SQLException
	{
		if (pst != null)
		{
			pst.close();
			pst = null;
		}
	}

	public static void close(ResultSet rs) throws SQLException
	{
		if (rs != null)
		{
			rs.close();
			rs = null;
		}
	}
	public final static boolean testConnection(String ip,String port,String database,String userName,String password,String driverName)throws SQLException
	{
		try
		{
			Class.forName(driverName);
		}
		catch (ClassNotFoundException e)
		{
			return false;
		}
		String url = String.format("jdbc:oracle:thin:@%s:%s:%s", ip,port,database);
		Connection conn = DriverManager.getConnection(url,userName,password);
		if(conn!=null)
		{
			conn.close();
			conn = null;
			return true;
		}
		return false;
	}
}
